# -*- coding: utf-8 -*-


# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://doc.scrapy.org/en/latest/topics/item-pipeline.html
import mysql.connector
from scrapyspider.items import Contest
from scrapyspider.items import Sponsor
from scrapyspider.items import Sort
from scrapyspider.items import BigType
from scrapyspider.items import Type
from scrapyspider.items import Urls
from scrapyspider.items import Contest_Continue
from scrapyspider.items import Sponsor_Continue
from scrapyspider.items import Contest_Continue1

class ScrapyspiderPipeline(object):
    def process_item(self, item, spider):
        return item

class IContestPipeline(object):
    def __init__(self):
        self.table = 'contest'
        self.conn = mysql.connector.connect(host='120.79.172.200',password='icontest',database='icontest',user='icontest')
        self.cursor = self.conn.cursor()

    def close_spider(self, spider):
        self.cursor.close()
        self.conn.close()

class ContestPipeline(IContestPipeline):
    def __init__(self):
        super(ContestPipeline,self).__init__()

    def process_item(self, item, spider):
        if(isinstance(item, Contest)):
            self.cursor.execute("insert into contest(contest_name,contest_start_time,contest_end_time,signup_start_time,signup_end_time,hot_degree,contest_url,sponsor_id) values(%s,%s,%s,%s,%s,%s,%s,%s) ",
                                (item['contest_name'],
                                 item['contest_start_time'],
                                 item['contest_end_time'],
                                 item['signup_start_time'],
                                 item['signup_end_time'],
                                 item['hot_degree'],
                                 item['contest_url'],
                                 item['sponsor_id'],
                                ))
            self.conn.commit()
        return item

    def close_spider(self, spider):
        super(ContestPipeline,self).close_spider(spider)
#
class SponsorPipeline(IContestPipeline):
    def __init__(self):
        super(ContestPipeline,self).__init__()

    def process_item(self, item, spider):
        if(isinstance(item, Sponsor)):
            self.cursor.execute("insert into contest(sponsor_id,sponsor_name,sponsor_password,sponsor_tel) values(%s,%s,%s,%s) ",
                                (item['sponsor_id'],
                                 item['sponsor_name'],
                                 item['sponsor_password'],
                                 item['sponsor_tel']
                                ))
            self.conn.commit()
        return item

    def close_spider(self, spider):
        super(ContestPipeline,self).close_spider(spider)

class SortPipeline(object):
    def __init__(self):
        self.conn = mysql.connector.connect(host='120.79.172.200', password='icontest', database='icontest',
                                            user='icontest')

    def process_item(self, item, spider):

        if(isinstance(item, Sort)):
            cursor_type = self.conn.cursor(buffered=True)
            cursor_sort = self.conn.cursor(buffered=True)
            cursor_contest = self.conn.cursor(buffered=True)
            cursor_type.execute('select type_id from type where type_name = %s', (item['type_name']))
            cursor_contest.execute('select contest_id from contest where contest_name = %s', (item['contest_name']))
            type_id = cursor_type.fetchone()
            contest_id = cursor_contest.fetchone()
            if type_id and contest_id:
                cursor_sort.execute('insert into sort(type_id, contest_id) VALUES(%s ,%s)', (type_id[0], contest_id[0]))
                self.conn.commit()
            cursor_type.close()
            cursor_sort.close()
            cursor_contest.close()
        return item

    def close_spider(self, spider):
        self.conn.close()

class bigType_typePipeline(IContestPipeline):
    def __init__(self):
        super(bigType_typePipeline,self).__init__()

    def process_item(self, item, spider):
        if(isinstance(item, BigType)):
            cursor1 = self.conn.cursor(buffered=True)
            cursor1.execute("insert into bigType (bigType_name) values ('{}')".format(item['bigType_name']))
            cursor1.close()
            self.conn.commit()
        if(isinstance(item, Type)):
            bigType_name = item['bigType_name']
            self.cursor.execute("select bigType_id from bigType where bigType_name = '{}'".format(bigType_name))
            bigType_id = self.cursor.fetchone()[0]
            c2 = self.conn.cursor()
            c2.execute("insert into type (bigType_id, type_name) values ('{}', '{}')".format(bigType_id, item['type_name']))
            self.conn.commit()
        return item

    def close_spider(self, spider):
        super(bigType_typePipeline,self).close_spider(spider)

class urlsSpiderPipeline(IContestPipeline):
    def __init__(self):
        super(urlsSpiderPipeline,self).__init__()

    def process_item(self, item, spider):
        if(isinstance(item, Urls)):
            self.cursor.execute("insert into urls (url) values ('{}')".format(item['url']))
            self.conn.commit()
        return item

    def close_spider(self, spider):
        super(urlsSpiderPipeline,self).close_spider(spider)

class contest_detail_img_urlPipeline(IContestPipeline):
    def __init__(self):
        super(contest_detail_img_urlPipeline,self).__init__()

    def process_item(self, item, spider):
        if(isinstance(item, Contest_Continue) and item['contest_detail'] != None and item['contest_img_url'] != None and  item['contest_name'] != None):
            self.cursor.execute("update contest set contest_detail = '{}', contest_img_url = '{}' where contest_name = '{}'".format(item['contest_detail'], item['contest_img_url'], item['contest_name']))
            self.conn.commit()
        return item
    def close_spider(self, spider):
        super(contest_detail_img_urlPipeline,self).close_spider(spider)

class sponsor_idPipeline(IContestPipeline):
    def __init__(self):
        super(sponsor_idPipeline,self).__init__()

    def process_item(self, item, spider):
        if(isinstance(item, Sponsor_Continue)):
            cursor1 = self.conn.cursor(buffered=True)
            cursor1.execute("insert into sponsor (sponsor_name, sponsor_password, sponsor_email, sponsor_tel) values (%s, %s, %s, %s)", (item['sponsor_name'], '1', '1', '1'))
            cursor1.execute("select sponsor_id from sponsor where sponsor_name = '{}'".format(item['sponsor_name']))
            sponsor_id = cursor1.fetchone()[0]
            cursor1.close()
            cursor2 = self.conn.cursor(buffered=True)
            cursor2.execute("update contest set sponsor_id = '{}' where contest_name = '{}'".format(sponsor_id, item['contest_name']))
            cursor2.close()
            self.conn.commit()
        return item

    def close_spider(self, spider):
        super(sponsor_idPipeline,self).close_spider(spider)

class hot_degreeSpider(IContestPipeline):
    def __init__(self):
        super(hot_degreeSpider,self).__init__()

    def process_item(self, item, spider):
        if(isinstance(item, Contest_Continue1)):
            self.cursor.execute("update contest set hot_degree = '{}' where contest_name = '{}'".format(item['hot_degree'], item['contest_name']))
            self.conn.commit()
        return item


    def close_spider(self, spider):
        super(hot_degreeSpider,self).close_spider(spider)